package merlin.managers;

import java.sql.*;
import java.util.*;

import merlin.beans.*;
import merlin.dao.*;
import merlin.utils.*;

public class ReportTitlesManager{

	public static DbResultSet selectReportTitlesRecords(String whereClause,String orderByClause){
		DbResultSet result = null;
		Connection con=null;
		ConnectionPool pool = null;
		try{
			pool = ConnectionPool.getConnectionPool();
			con = pool.getConnection();

			ReportTitlesDAO dao = new ReportTitlesDAO(con);
			result = dao.selectReportTitlesRecords(whereClause,orderByClause);
		}catch(Exception e){
			System.err.println(e);
			throw new RuntimeException(e);
		}finally{
			pool.returnConnection(con);
			con=null;
		}
		return result;
	}

	public static ReportTitlesBean selectReportTitlesByPK(ReportTitlesBean b){
		ReportTitlesBean bean = null;
		Connection con=null;
		ConnectionPool pool = null;
		try{
			pool = ConnectionPool.getConnectionPool();
			con = pool.getConnection();

			ReportTitlesDAO dao = new ReportTitlesDAO(con);
			bean = dao.selectReportTitlesByPK(b);
		}catch(Exception e){
			System.err.println(e);
			throw new RuntimeException(e);
		}finally{
			pool.returnConnection(con);
			con=null;
		}
		return bean;
	}

	/**
	 * Returns the report title names in a sorted map with the keys as the
	 * first alphabet of the report titles and the value as a list of titles
	 * that start with that particular alphabet.
	 * Ex.
	 * 	{'A',['Air Filter','Air Space']}
	 * 	{'B',['Baloons','Bars']}
	 */
	public static Map selectReportTitlesInSortedMap(List result){
		Map mapTitles = new TreeMap();

		for(int i=0; i < result.size();i++){
   			ReportTitlesBean bean = (ReportTitlesBean)result.get(i);
   			String strCapitalized = MerlinUtilities.capitalizeFully(bean.getReportTitle());
   			bean.setReportTitle(strCapitalized);
   			Character chrFirstChar = new Character(strCapitalized.charAt(0));
 			List lstReports = (List)mapTitles.get(chrFirstChar);
 			if(lstReports == null)
 				lstReports = new ArrayList();
 			lstReports.add(bean);
 			mapTitles.put(chrFirstChar,lstReports);
		}

		// make sure that each of the lists contain values in multiple of 3
		Iterator iterator = mapTitles.keySet().iterator();
		while(iterator.hasNext()){
			Object key = iterator.next();
			List values = (List)mapTitles.get(key);
			int remainder = values.size() % 3;

			if(remainder != 0){
				while(remainder < 3){
    				values.add(null);
    				remainder++;
				}
			}
		}
		return mapTitles;
	}

	/**
	 *
	 */
	public static Map selectReportTitlesInSortedMap(){
		// The report 'AAA' was added in the table because a bug in Frontbase causes the
		// first record in the table to be skipped while indexing, so a dummy record 'AAA'
		// was added. During the display of the report titles, this record needs to be removed.

		// New Addition : Only those reports whose CUSTOMISED_INDICATOR is not TRUE are to be shown.
		List result = selectReportTitlesRecords("REPORT_TITLE <> 'AAA' and coalesce(CUSTOMISED_INDICATOR,false) <> true","REPORT_TITLE ASC");
		return selectReportTitlesInSortedMap(result);
	}


    /**
	 * Formats the given map into a table.
	 *
	 */
	public static String convertToTable(Map mapTitles){
		StringBuffer  sbTable = new StringBuffer();
		sbTable.append("<table width='100%' border = '0' class='tblReports'>");

		Iterator iterator = mapTitles.keySet().iterator();

		while(iterator.hasNext()){
			Object key = iterator.next();
			List values = (List)mapTitles.get(key);
			sbTable.append("<tr><td colspan='6'><a name='").append(key).append("'>&nbsp;</a></td></tr>");
			int i = 1;
			for(int j=0;j<values.size();j++){
				ReportTitlesBean bean = (ReportTitlesBean)values.get(j);

				if(i == 1)sbTable.append("<tr>");
				if(bean != null){
					sbTable.append("<td class='reportChkBox'><input type='checkbox' class='rptCheckbox' name='chkIndustry' value='").append(bean.getReportTitlesId()).append("' id='idChkIndustry").append(bean.getReportTitlesId()).append("'></td><td class='reportName' title='" + bean.getNoOfCompaniesInDatabase() + " firms in report'><label for='idChkIndustry").append(bean.getReportTitlesId()).append("'>").append(bean.getReportTitle()).append("</label></td>");
				}else{
					sbTable.append("<td class='reportName'>&nbsp;</td><td class='reportName'>&nbsp;</td>");
				}
				if(i==3){
					i = 1;
					sbTable.append("</tr>");
				}else{
					i++;
				}
			}
		}
		sbTable.append("</table>");

		return sbTable.toString();
	}

	/**
	 * Returns a table of reports.
	 */
	public static String getReportsTable(){
		Map mapTitles = ReportTitlesManager.selectReportTitlesInSortedMap();
		return convertToTable(mapTitles);
	}

	/**
	 * Implemention of Google Landing page. The input is a bunch of keywords, based on which
	 * a result of report titles need to he created,with the most appropriate results sorted
	 * according to a ranking algorithm.
	 */
	public static DbResultSet getGoogleLandingPageResult(String searchText){
		ConnectionPool pool = null;
		Connection con=null;
		DbResultSet result = new DbResultSet();
		try{
			pool = ConnectionPool.getConnectionPool();
			con = pool.getConnection();

			ReportTitlesDAO dao = new ReportTitlesDAO(con);

        	String searchWords[] = searchText.split(" ");
        	int scores[] = {4,2,1,1};

			// search  a max of 4 words.
            for(int i=0;i<searchWords.length && i < scores.length; i++){
				String sqlWhereClause= " SATISFIES(NSD.IDX_REPORT_DESCRIPTION,'" + searchWords[i] + "') ";
				DbResultSet dbRes  = dao.selectReportTitlesRecords(sqlWhereClause,"REPORT_TITLE");

				// add/update the newly found resords into the existing result list.
    			for(int j=0;j<dbRes.size();j++){
	    			boolean found = false;
	    			ReportTitlesBean bean = (ReportTitlesBean)dbRes.get(j);
	    			for(int k=0;k<result.size();k++){
		    			ReportTitlesBean ebean = (ReportTitlesBean)result.get(k);
		    			if(bean.getReportTitlesId() == ebean.getReportTitlesId()){
			    			found = true;
			    			ebean.setScore(ebean.getScore() + scores[i]);
			    			break;
						}
					}
					if(!found){
      					bean.setScore(scores[i]);
      					result.add(bean);
					}
				}
			}

			// sort the results according to their scores,as the ReportTitlesBean implements Comparable.
			Collections.sort(result);

		}catch(Exception e){
			System.err.println(e);
			throw new RuntimeException(e);
		}finally{
			pool.returnConnection(con);
			con=null;
		}
		return result;
	}
	
	public static ReportTitlesBean selectReportTitleByFreeReportDownloadRecordId(int recordId){
		ReportTitlesBean bean = null;
		Connection con=null;
		ConnectionPool pool = null;
		try{
			pool = ConnectionPool.getConnectionPool();
			con = pool.getConnection();

			ReportTitlesDAO dao = new ReportTitlesDAO(con);
			bean = dao.selectReportTitleByFreeReportDownloadRecordId(recordId);
		}catch(Exception e){
			System.err.println(e);
			throw new RuntimeException(e);
		}finally{
			pool.returnConnection(con);
			con=null;
		}
		return bean;
	}
}